using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL.NhaDat;

namespace SouthernLand.Common.DAL.NhaDat
{
    public class CategoriesDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new Categories instance filled with the DataReader's current record data 
        /// </summary>
        private Categories GetCategoriesFromReader(IDataReader dr)
        {
            var obj = new Categories();
            obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue : (Int32) dr["CateID"];
            obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime) dr["AddedDate"];
            obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty : (String) dr["AddedBy"];
            obj.AppKey = (dr["AppKey"] is DBNull) ? String.Empty : (String) dr["AppKey"];
            obj.Title = (dr["Title"] is DBNull) ? String.Empty : (String) dr["Title"];
            obj.Title_en = (dr["Title_en"] is DBNull) ? String.Empty : (String) dr["Title_en"];
            obj.Description = (dr["Description"] is DBNull) ? String.Empty : (String) dr["Description"];
            obj.Description_en = (dr["Description_en"] is DBNull) ? String.Empty : (String) dr["Description_en"];
            obj.ImageUrl = (dr["ImageUrl"] is DBNull) ? String.Empty : (String) dr["ImageUrl"];
            obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue : (Int32) dr["DisplayOrder"];
            obj.ParentID = (dr["ParentID"] is DBNull) ? Int32.MinValue : (Int32) dr["ParentID"];
            obj.IsActive = (dr["IsActive"] is DBNull) ? false : (Boolean) dr["IsActive"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of Categories with the data read from the input DataReader
        /// </summary>
        private List<Categories> GetCategoriesCollectionFromReader(IDataReader dr)
        {
            var obj = new List<Categories>();
            while (dr.Read())
                obj.Add(GetCategoriesFromReader(dr));
            return obj;
        }

        public List<Categories> GetCategoriesByParentID(Int32 ParentID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_GetCategoriesByParentID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetCategoriesCollectionFromReader(dr);
            }
        }

        public Int32 GetAutoCateID()
        {
            Int32 value;
            try
            {
                using (SqlConnection myConn = GetConnectionString())
                {
                    var myCommand = new SqlCommand("w4u_Categories_GetAutoCateID", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;

                    value = ((Int32) myCommand.ExecuteScalar() + 1);
                    if (value == 1)
                        value = 2; // vi khong de trung voi ParentID = 1

                    return value;
                }
            }
            catch
            {
                return 2;
            }
        }

        public int Insert(Categories obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = GetAutoCateID();
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Title_en", SqlDbType.NVarChar).Value = obj.Title_en;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = obj.Description;
                myCommand.Parameters.Add("@Description_en", SqlDbType.NVarChar).Value = obj.Description_en;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = obj.ImageUrl;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = obj.ParentID;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                return myCommand.ExecuteNonQuery();
            }
        }

        public bool Update(Categories obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                myCommand.Parameters.Add("@AppKey", SqlDbType.VarChar).Value = obj.AppKey;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Title_en", SqlDbType.NVarChar).Value = obj.Title_en;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = obj.Description;
                myCommand.Parameters.Add("@Description_en", SqlDbType.NVarChar).Value = obj.Description_en;
                myCommand.Parameters.Add("@ImageUrl", SqlDbType.NVarChar).Value = obj.ImageUrl;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
                myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = obj.ParentID;
                myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the w4u_Categories with the specified ID
        /// </summary>
        public Categories GetCategoriesByID(Int32 CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_GetCategoriesByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetCategoriesFromReader(dr) : null);
            }
        }

        //Get All Categories
        public List<Categories> GetAllCategories(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_GetAllCategories", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetCategoriesCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  Categories
        public int GetCountCategories()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_Categories_GetCountCategories", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}